/**
 * 项目名：phms3
 * 日期：    2019-11-22 17:31:07
 * Copyright (c) 2015- HTSC-版权所有
 */

package com.joysuch.wwyt.mh.repository;

import com.joysuch.wwyt.mh.entity.MhMonitoringPoint;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import java.util.List;
import java.util.Map;

/**
 * MhMonitoringPointDao
 */
public interface MhMonitoringPointDao
        extends JpaRepository<MhMonitoringPoint, Long>, QuerydslPredicateExecutor<MhMonitoringPoint> {

    Page<MhMonitoringPoint> findAll(Specification<MhMonitoringPoint> spec, Pageable pageable);

    MhMonitoringPoint save(MhMonitoringPoint bean);

    void delete(MhMonitoringPoint bean);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND POINT_CODE = ?1", nativeQuery = true)
    MhMonitoringPoint findByPointCode(String pointCode);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND TENENT_ID = ?1 AND ORG_CODE = ?2 AND POINT_CODE = ?3", nativeQuery = true)
    MhMonitoringPoint findByTenentIdAndOrgCodeAndPointCode(Long tenantId, String orgCode, String pointCode);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND TENENT_ID = ?1 AND ORG_CODE = ?2", nativeQuery = true)
    List<MhMonitoringPoint> findByTenentIdAndOrgCode(Long tenantId, String orgCode);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND MONITORING_TYPE_ID = ?1", nativeQuery = true)
    List<MhMonitoringPoint> findByMhMonitoringType(Long type);

    //@Query("select distinct( m) from MhMonitoringPoint m join BpDeviceFacilityMonitoringPoint b on m.id = b.monitoringPointId where m.mhMonitoringType=?1 ")
    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND MONITORING_TYPE_ID = ?1", nativeQuery = true)
    List<MhMonitoringPoint> selectBindedPointsByType(Long type);

    // @Query("select distinct( m) from MhMonitoringPoint m join BpDeviceFacilityMonitoringPoint b on m.id = b.monitoringPointId where m.mhMonitoringType in (?1) ")
    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND MONITORING_TYPE_ID IN (?1)", nativeQuery = true)
    List<MhMonitoringPoint> selectBindedPointsByTypes(List<Long> type);

    // @Query(value = "select distinct tenent_id as tenentId, org_code as orgCode from wwyt_mh_monitoring_point", nativeQuery = true)
    @Query(value = "SELECT DISTINCT TENENT_ID AS tenentId,ORG_CODE AS orgCode FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0)", nativeQuery = true)
    List<Map<String, Object>> selectTenentIdAndOrgCodes();

    //@Query(value = "select count(m) from  MhMonitoringPoint m where pointCode=?1")
    @Query(value = "SELECT COUNT(*) FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND POINT_CODE = ?1", nativeQuery = true)
    Long countOfCode(String code);

    // @Query(value = "select count(m) from  MhMonitoringPoint m where pointCode=?1 and id != ?2")
    @Query(value = "SELECT COUNT(*) FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND POINT_CODE = ?1 AND ID != ?2", nativeQuery = true)
    Long countOfCodeExclude(String code, Long excludeId);

    // @Query("select distinct( m) from MhMonitoringPoint m  where m.mhMonitoringType in (?1) ")
    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND MONITORING_TYPE_ID IN (?1)", nativeQuery = true)
    List<MhMonitoringPoint> findAllair(List<Long> list);

    @Query(value = "SELECT p.* FROM wwyt_mh_monitoring_point p WHERE p.DELETE_FLAG='0' AND (p.show_state IS NULL OR p.show_state !=0)", nativeQuery = true)
    List<MhMonitoringPoint> findAll();

    @Query(value = "SELECT count(1) FROM wwyt_mh_monitoring_point p WHERE p.DELETE_FLAG='0' AND (p.show_state IS NULL OR p.show_state !=0)", nativeQuery = true)
    Long findCount();

    @Query(value = "select * from wwyt_mh_monitoring_point p left join wwyt_mh_monitoring_type t on p.MONITORING_TYPE_ID = t.id " +
            " where p.DELETE_FLAG='0' AND (p.show_state IS NULL OR p.show_state !=0) and " +
            "p.tenent_id =:tenentId and p.org_code like :orgCode% and t.DELETE_FLAG='0' and t.tenent_id =:tenentId " +
            "and t.org_code like :orgCode% and t.CATEGORY_ID = 1", nativeQuery = true)
    List<MhMonitoringPoint> findAllYouDuKeRanPoint(Long tenentId, String orgCode);

    @Query(value = "SELECT COUNT(ID) FROM wwyt_mh_monitoring_point WHERE MONITORING_TYPE_ID =?1 AND DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0)", nativeQuery = true)
    int countByMonitoringTypeIdAndShowState(Long id);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point " +
            "WHERE MONITORING_TYPE_ID IN(SELECT id FROM wwyt_mh_monitoring_type WHERE CATEGORY_ID = ?1 AND delete_flag = '0') " +
            "AND delete_flag = '0' AND (show_state IS NULL OR show_state !=0) and (location is not null or location != '')", nativeQuery = true)
    List<MhMonitoringPoint> getEnvironmentPoints(long l);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point where id= ?1 and delete_flag = '0' AND (show_state IS NULL OR show_state !=0) " , nativeQuery = true)
    MhMonitoringPoint findFirstByIdAndState(Long id);

    @Query(value = "SELECT p.* FROM wwyt_mh_monitoring_point p WHERE p.LOCATION is not null AND  p.DELETE_FLAG = '0' AND (p.show_state IS NULL OR p.show_state != 0)", nativeQuery = true)
    List<MhMonitoringPoint> findAllHaveLocation();

    @Modifying
    @Query("update MhMonitoringPoint p set p.state = ?1 where p.id in (?2)")
    void batchSwitch(Integer state,List<Long> ids);

    @Query(value = "SELECT * FROM wwyt_mh_monitoring_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=0) AND id IN (?1)", nativeQuery = true)
    List<MhMonitoringPoint> findByIds(List<Long> ids);
}
